﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using Ortodoctor.Componentes;
using System.Web;

namespace Ortodoctor.modelo
{
    class modeloFuncionario : conexao
    {
        public modeloFuncionario()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public int insert(Ortodoctor.Componentes.Funcionario f)
        {
            object obj;
            try
            {
                //con.open();
                String sql = "INSERT INTO funcionario(nome, data_admissao, cpf, rg, id_clinica) " +
                             "VALUES (:p1, :p2, :p3, :p4, :p5)";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", f.Nome);
                com.Parameters.AddWithValue("p2", f.DataAdmissao);
                com.Parameters.AddWithValue("p3", f.Cpf);
                com.Parameters.AddWithValue("p4", f.Rg);
                com.Parameters.AddWithValue("p5", f.IDClinica);
                obj = com.ExecuteScalar();
            }
            finally
            {
                
                conn.Close();
            }
            return obj != null ? (int.Parse(obj.ToString())) : getMaxId();
        }

        public int getMaxId()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT max(id_funcionario) as id_funcionario from funcionario";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally { conn.Close(); }
            return Convert.ToInt32(dt.Rows[0]["id_funcionario"]);
        }

        public DataTable getAll()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM funcionario inner join clinica using(id_clinica) inner join endereco using(id_endereco)";
                if (!HttpContext.Current.Session["USUARIO_ACESSO"].ToString().Equals("2"))
                    sql = "SELECT * FROM funcionario inner join clinica using(id_clinica) inner join endereco using(id_endereco) where id_clinica=" + HttpContext.Current.Session["ID_CLINICA"].ToString();
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getFuncionario(int id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM funcionario left join clinica using(id_clinica) inner join endereco using (id_endereco) WHERE id_funcionario=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public void update(Ortodoctor.Componentes.Funcionario f)
        {
            try
            {
                //con.open();
                String sql = @"UPDATE funcionario SET nome=:n, data_admissao=:d, cpf=:c, rg=:r, id_clinica=:id_clinica , id_usuario_modificacao=:id_mod
                          WHERE id_funcionario=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("n", f.Nome);
                com.Parameters.AddWithValue("d", f.DataAdmissao);
                com.Parameters.AddWithValue("c", f.Cpf);
                com.Parameters.AddWithValue("r", f.Rg);
                com.Parameters.AddWithValue("id_clinica", f.IDClinica);
                com.Parameters.AddWithValue("id", f.IdFuncionario);
                com.Parameters.AddWithValue("id_mod", HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void delete(int id)
        {
            try
            {
                //con.open();
                String sql = "DELETE FROM funcionario WHERE id_funcionario=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.ExecuteNonQuery();

                sql = "update funcionario_hist set id_usuario_modificacao = :id_mod, data_modificacao = :data WHERE id_funcionario=:id";
                com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.Parameters.AddWithValue("id_mod", HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.Parameters.AddWithValue("data", DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void Log(string operacao, string log)
        {
            DateTime dt = DateTime.Now;
            try
            {
                String sql = @"INSERT INTO funcionario_log(operacao, log, data) values (:p1, :p2, :p3)";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", operacao);
                com.Parameters.AddWithValue("p2", log);
                com.Parameters.AddWithValue("p3", DateTime.Now.ToShortDateString() + " - " + DateTime.Now.ToShortTimeString());
                com.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
